#!/usr/bin/env python2.6
"""
Scan database dumps and fill sqlite database (file database.db) with the data.

Database dumps are:
  * libjoinedbooks.sql
  * libbook.sql
"""

from pysqlite2 import dbapi2 as sqlite
import os.path
import re
import sys

def smart_split(s):
    elements = []
    buffer = ""
    is_string = False
    prev_sym = ""
    for x in v:
        if x == "'" and prev_sym != "\\":
            is_string = not is_string
            
        if x == "," and not is_string:
            # flush buffer
            if buffer[0] == "'" and buffer[-1] == "'":
                buffer = buffer[1:-1].replace(r"\'", "'")
                
            elements.append(buffer)
            buffer = ""
            continue
        
        buffer += x
        prev_sym = x
    
    elements.append(buffer)
    
    return elements

# create temporary table
db = sqlite.connect("database.db")
cursor = db.cursor()

queries = ("DROP TABLE IF EXISTS libbook ", "DROP TABLE IF EXISTS libjoinedbooks", 
           "DROP INDEX IF EXISTS libbook_filename")
for query in queries:
    cursor.execute(query)
    

# parse table with joined books
#  Id INTEGER NOT NULL,
query = """
CREATE TABLE libjoinedbooks (
  Time timestamp NOT NULL,
  BadId int(11) NOT NULL default '0',
  GoodId int(11) NOT NULL default '0'
)
"""
cursor.execute(query)

f = open(os.path.join("bases", "libjoinedbooks.sql"), "r")
for line in f:
    if line.startswith("INSERT INTO `libjoinedbooks` VALUES"):
        line = unicode(line, "UTF-8")
        values = line.split("),(")
        
        values[0] = values[0].replace("INSERT INTO `libjoinedbooks` VALUES (", "")
        values[-1] = values[-1].replace(");", "")
        
        for v in values:
            # split
            elements = smart_split(v)
            
            if len(elements) != 3:
                print "Invalid number of elements: ", elements
                
            query = "INSERT INTO libjoinedbooks VALUES (%s)" % ",".join(["?" for x in xrange(3)])
            cursor.execute(query, elements)

f.close()

#db.commit()
#sys.exit()

# parse libbook dump file to extract filenames associated with the BookId
'''
query = """
CREATE TABLE `libbook` (
  `bid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FileSize` int(10) unsigned NOT NULL DEFAULT '0',
  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Title` varchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Title1` varchar(254) CHARACTER SET utf8 NOT NULL,
  `Lang` char(2) CHARACTER SET utf8 NOT NULL DEFAULT 'ru',
  `FileType` char(4) CHARACTER SET utf8 NOT NULL,
  `Year` smallint(6) NOT NULL DEFAULT '0',
  `Deleted` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Ver` varchar(8) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `FileAuthor` varchar(64) CHARACTER SET utf8 NOT NULL,
  `keywords` varchar(255) CHARACTER SET utf8 NOT NULL,
  `Blocked` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `md5` binary(32) NOT NULL,
  `Broken` char(1) COLLATE utf8_unicode_ci NOT NULL COMMENT 'mark bad fb2',
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
) 
"""
'''
query = """
CREATE TABLE libbook (
  BookId INTEGER NOT NULL,
  Deleted INTEGER,
  PRIMARY KEY (BookId)
)
"""
cursor.execute(query)


f = open(os.path.join("bases", "libbook.sql"), "r")

for line in f:
    if line.startswith("INSERT INTO `libbook` VALUES"):
        line = unicode(line, "UTF-8")
        values = line.split("),(")
        
        values[0] = values[0].replace("INSERT INTO `libbook` VALUES (", "")

        values[-1] = values[-1].replace(");", "")
        
        for v in values:
            # split
            elements = smart_split(v)
            
            if len(elements) != 16:
                print "Invalid number of elements: ", elements
                
            query = "INSERT INTO libbook VALUES (?, ?)"
            deleted = 0
            if elements[8] == '1':
                deleted = 1

            cursor.execute(query, (elements[0], deleted))
            
f.close()


db.commit()
